===== SLIDE 1 ===== 单元5 Hive数据查询 ===== SLIDE 2 ===== 学习目标/Target 熟悉SELECT语句，能够描述并列举其语法结构中各关键字和子句的作用。 掌握条件查询，能够在查询语句中灵活运用各类运算符设置查询条件。 熟悉公共表表达式，能够在查询语句中定义临时结果集并实现多次引用。 掌握分组查询，能够熟练运用GROUP BY子句对数据进行分组统计。 ===== SLIDE 3 ===== 学习目标/Target 掌握排序查询，能够熟练运用ORDER BY、SORT BY等子句对数据进行排序展示。 了解合并查询，能够使用UNION语句对两张表的数据进行合并。 掌握连接查询，能够熟练运用JOIN语句实现内连接、左外连接、右外连接等操作。 了解抽样查询，能够使用字节数抽样、行抽样等方式从表中查询部分数据。 ===== SLIDE 4 ===== 单元概述/ Summary Hive的数据定义语言和数据操作语言解决了数据的存储与维护问题，使数据能够按照既定模式保存在数据仓库中。但数据仓库的根本目的不仅是存储，更重要的价值在于支持分析与决策。换句话说，数据只有经过高效的查询与分析，才能真正发挥价值。因此，掌握 Hive的数据查询语言（Data Query Language，DQL）是数据仓库应用的核心。本单元将详细介绍Hive数据查询。 ===== SLIDE 5 ===== 目录/Contents 01 02 03 任务：分析SELECT语句 任务：执行条件查询 任务：执行公共表表达式查询 04 任务：执行分组查询 ===== SLIDE 6 ===== 目录/Contents 05 06 07 任务：执行排序查询 任务：执行合并查询 任务：执行连接查询 08 任务：执行抽样查询 ===== SLIDE 7 ===== 任务：分析SELECT语句 5.1 ===== SLIDE 8 ===== 5.1 任务：分析SELECT语句 熟悉SELECT语句，能够描述并列举其语法结构中各关键字和子句的作用。 学习目标 ===== SLIDE 9 ===== 在Hive的数据查询语言中，最基础且最常用的语句是SELECT。几乎所有查询操作都依赖于它，无论是简单的数据检索，还是复杂的聚合、分组与排序等，SELECT语句都是构建查询的核心。可以说，熟练掌握SELECT语句的语法格式，是学习Hive数据查询语言的起点。 5.1 任务：分析SELECT语句 ===== SLIDE 10 ===== 示例命令 关于Hive中SELECT语句的语法格式如下。 [WITH CommonTableExpression (,CommonTableExpression)*] SELECT [ALL | DISTINCT] select_expr,select_expr,... FROM table_reference [WHERE where_condition] [GROUP BY group_col_list] [ORDER BY order_col_list] [CLUSTER BY cluster_col_list | [DISTRIBUTE BY distribute_col_list] [SORT BY sort_col_list] ] [LIMIT [offset,] rows]; 5.1 任务：分析SELECT语句 ===== SLIDE 11 ===== WITH子句为可选，用于定义公共表表达式（Common Table Expression，CTE）。 5.1 任务：分析SELECT语句 1.WITH子句 ALL关键字为默认，表示返回查询结果中的所有记录，包括重复值。 2.ALL关键字 DISTINCT关键字为可选，用于对查询结果去重，仅返回唯一的记录组合。 3. DISTINCT关键字 2.ALL关键字 ===== SLIDE 12 ===== select_expr参数用于指定查询内容，可以是字段、字面量、表达式、函数等。如果查询内容指定为单个*字符，表示查询所有字段。 查询内容可配合AS子句为查询内容指定别名，别名会显示在查询结果中。 5.1 任务：分析SELECT语句 4. select_expr参数 ===== SLIDE 13 ===== 当查询内容为复杂数据类型时，可以直接利用Hive提供的复杂类型运算符访问其内部元素。关于Hive中复杂类型运算符的介绍如表。 5.1 任务：分析SELECT语句 4. select_expr参数 复杂类型运算符 描述 示例 A[n] A为ARRAY类型的字段，n为元素的下标。该运算符用于返回A的第n个元素 items[0] M[key] M为MAP类型的字段，key为键值对的键。该运算符用于返回M中key对应的值 attrs['sku'] S.x S为STRUCT类型的字段，x为STRUCT内部的字段。该运算符用于返回S中x的值 user_info.city ===== SLIDE 14 ===== FROM子句用于指定查询的数据来源。table_reference参数可以是单表、多表、子查询、视图等。需要说明的是，若查询的数据来源为子查询，则子查询需要使用小括号包裹且必须指定别名。在外层查询中，通过“别名.字段”的格式获取子查询结果中相应的字段，示例代码如下。 5.1 任务：分析SELECT语句 5. FROM子句 SELECT t.order_id,t.commodity FROM ( SELECT order_id,items[0] AS commodity FROM chapter4.orders) t; 示例代码 ===== SLIDE 15 ===== WHERE子句为可选，用于设置筛选条件，仅返回满足条件的记录。 5.1 任务：分析SELECT语句 6. WHERE 子句 GROUP BY子句为可选，用于根据指定字段的值对查询结果进行分组。 7. GROUP BY 子句 ORDER BY子句为可选，用于根据指定字段的值对查询结果进行全局排序。 8. ORDER BY 子句 ===== SLIDE 16 ===== CLUSTER BY子句为可选，用于根据指定字段的值将查询结果分发到不同的Reduce，并在每个Reduce内部根据指定字段的值对数据进行排序。CLUSTER BY子句的分发规则与DISTRIBUTE BY子句相同。 在Hive On Tez的语境下，Reduce个数可以理解为经由Shuffle边（Edge）连接的下游Vertex（顶点）的并行Task（任务）个数。 5.1 任务：分析SELECT语句 9. CLUSTER BY 子句 ===== SLIDE 17 ===== DISTRIBUTE BY子句为可选，用于根据指定字段的值将查询结果分发到不同的Task。分发规则是对指定字段的值进行哈希取模运算，哈希取模结果相同的记录会被分配到同一个Task。 关于哈希取模运算的公式如下。 5.1 任务：分析SELECT语句 10. DISTRIBUTE BY 子句 hash(col) % task_num 运算公式 表示DISTRIBUTE BY子句中指定的字段 表示Task个数 ===== SLIDE 18 ===== SORT BY子句为可选，用于在每个Task内部根据指定字段的值对数据进行排序。 5.1 任务：分析SELECT语句 11. SORT BY 子句 LIMIT子句为可选，用于限制查询结果返回的行数。当指定该子句时，仅返回查询结果的前N行。rows参数用于指定返回的行数。offset参数为可选，用于指定从查询结果的第offset+1行开始返回指定的行数。 12. LIMIT 子句 ===== SLIDE 19 ===== 接下来，演示如何使用SELECT语句实现基本查询，从chapter4数据库中的分区表orders查询数据，具体HiveQL语句如下。 5.1 任务：分析SELECT语句 SELECT order_id, items[0] AS commodity, user_client.device, current_timestamp AS select_time FROM chapter4.orders; HiveQL语句 ===== SLIDE 20 ===== 上述语句执行完成后的效果如图。 5.1 任务：分析SELECT语句 ===== SLIDE 21 ===== 任务：执行条件查询 5.2 ===== SLIDE 22 ===== 5.2 任务：执行条件查询 掌握条件查询，能够在查询语句中灵活运用各类运算符设置查询条件。 学习目标 ===== SLIDE 23 ===== 在上一节中，我们学习了SELECT语句的基本用法，能够根据指定的查询内容从表中提取数据。然而，在实际业务分析中，用户往往更关心满足特定条件的结果。例如，仅查询销售额大于1000元的订单、仅查询某类商品的销售情况、仅查询特定分区的数据等。 如果依旧使用基础的SELECT语句，就需要先返回整个表的数据，再人工筛选所需部分。这不仅效率低下，还会造成资源浪费。 5.2 任务：执行条件查询 ===== SLIDE 24 ===== 为了解决这一问题，Hive提供了条件查询机制，在SELECT语句中通过WHERE子句设置筛选条件，从而在查询阶段就过滤掉无关数据。这样既能显著提升查询效率，又能让结果更贴合分析需求。 5.2 任务：执行条件查询 ===== SLIDE 25 ===== 在使用WHERE子句时，筛选条件通常需要借助不同类型的运算符来表达筛选规则，主要包括关系运算符、算术运算符和逻辑运算符，具体介绍如下。 关系运算符 A 算术运算符 B 逻辑运算符 C 5.2 任务：执行条件查询 ===== SLIDE 26 ===== 在条件查询中，我们常常需要判断数据之间的关系。例如，订单金额是否达到某个阈值、日期是否处于指定区间、字段是否为空等。尽管这些问题的表现形式不同，但本质上都是对值之间关系的判定。为此，Hive提供了一系列关系运算符来表达值之间的关系。 1. 关系运算符 5.2 任务：执行条件查询 ===== SLIDE 27 ===== 关系运算符广泛应用于WHERE、HAVING、JOIN等子句中，它们直接决定记录是否被选中，是构建任何筛选条件的基础。关于Hive中关系运算符的介绍如表。 1. 关系运算符 5.2 任务：执行条件查询 关系运算符 语法 操作数类型 描述 = A = B 原生数据类型 用于判断A和B是否相等。若相等，则返回TRUE；否则返回FALSE。如果A或B为NULL，那么返回NULL，表示未知（UNKNOWN）。例如，product_id = 1001 == A == B 原生数据类型 与=作用相同 <=> A <=> B 原生数据类型 与=作用相同。不同之处在于，如果A和B均为NULL，那么返回TRUE。例如，phone <=> NULL ===== SLIDE 28 ===== 1. 关系运算符 5.2 任务：执行条件查询 关系运算符 语法 操作数类型 描述 <> A <> B 原生数据类型 用于判断A和B是否不相等。若不相等，则返回TRUE；否则返回FALSE。如果A或B为NULL，那么返回NULL。例如，product_id <> 1001 != A != B 原生数据类型 与<>作用相同 < A < B 原生数据类型 用于判断A是否小于B。若小于，则返回TRUE；否则返回FALSE。如果A或B为NULL，那么返回NULL。例如，amount < 500 <= A <= B 原生数据类型 用于判断A是否小于或等于B。若小于或等于，则返回TRUE；否则返回FALSE。如果A或B为NULL，那么返回NULL。例如，amount <= 500 ===== SLIDE 29 ===== 1. 关系运算符 5.2 任务：执行条件查询 关系运算符 语法 操作数类型 描述 > A > B 原生数据类型 用于判断A是否大于B。若大于，则返回TRUE；否则返回FALSE。如果A或B为NULL，那么返回NULL。例如，amount > 500 >= A >= B 原生数据类型 用于判断A是否大于或等于B。若大于或等于，则返回TRUE；否则返回FALSE。如果A或B为NULL，那么返回NULL。例如，amount >= 500 IS NULL A IS NULL 所有数据类型 用于判断A是否为NULL。若A为NULL，则返回TRUE；否则返回FALSE。例如，email IS NULL IS NOT NULL A IS NOT NULL 所有数据类型 用于判断A是否不为NULL。若A为非NULL，则返回TRUE；否则返回FALSE。例如，email IS NOT NULL ===== SLIDE 30 ===== 1. 关系运算符 5.2 任务：执行条件查询 关系运算符 语法 操作数类型 描述 BETWEEN AND A BETWEEN B AND C 原生数据类型 用于判断A是否在区间[B,C]内。若在区间内，则返回TRUE；否则返回FALSE。例如，amount BETWEEN 500 AND 1000 NOT BETWEEN AND A NOT BETWEEN B AND C 原生数据类型 用于判断A是否不在区间[B,C]内。若不在区间内，则返回TRUE；否则返回FALSE。例如，amount NOT BETWEEN 500 AND 1000 IS TRUE A IS TRUE BOOLEAN类型 用于判断A是否为TRUE。若A为TRUE，则返回TRUE；否则返回FALSE。例如，is_success IS TRUE ===== SLIDE 31 ===== 1. 关系运算符 5.2 任务：执行条件查询 关系运算符 语法 操作数类型 描述 IS FALSE A IS FALSE BOOLEAN类型 用于判断A是否为FALSE。若A为FALSE，则返回TRUE；否则返回FALSE。例如，is_success IS FALSE IS NOT TRUE A IS NOT TRUE BOOLEAN类型 用于判断A是否不为TRUE。若A不为TRUE，则返回TRUE；否则返回FALSE。例如，is_success IS NOT TRUE IS NOT FALSE A IS NOT FALSE BOOLEAN类型 用于判断A是否不为FALSE。若A不为FALSE，则返回TRUE；否则返回FALSE。例如，is_success IS NOT FALSE ===== SLIDE 32 ===== 1. 关系运算符 5.2 任务：执行条件查询 关系运算符 语法 操作数类型 描述 LIKE A LIKE B 字符串类型 用于判断A与B是否匹配，其中B为包含通配符的模式（Pattern）。若匹配，则返回TRUE，否则返回FALSE。例如，name LIKE 'A%' NOT LIKE A NOT LIKE B 字符串类型 用于判断A与B是否不匹配。若不匹配，则返回TRUE，否则返回FALSE。例如，name NOT LIKE 'A%' RLIKE A RLIKE B 字符串类型 用于判断A与B是否匹配，其中B为正则表达式。若匹配，则返回TRUE，否则返回FALSE。例如，name RLIKE '^A.*n$' REGEXP A REGEXP B 字符串类型 与RLIKE作用相同 ===== SLIDE 33 ===== 接下来，演示如何使用关系运算符表中的部分关系运算符进行条件查询，具体内容如下。 5.2 任务：执行条件查询 1. 关系运算符 ===== SLIDE 34 ===== 为了便于演示其他类型运算符的使用，本任务将在Hive中创建一个名为chapter5的数据库，并在该数据库下创建一个名为sales_fact的分区表。然后，将示例数据加载到分区表sales_fact，具体操作步骤如下。 5.2 任务：执行条件查询 1. 关系运算符 STEP 01 ① 在Hive中创建数据库chapter5，具体HiveQL语句如下。 CREATE DATABASE IF NOT EXISTS chapter5; ===== SLIDE 35 ===== STEP 01 ② 在数据库chapter5中创建分区表sales_fact，具体HiveQL语句如下。 5.2 任务：执行条件查询 1. 关系运算符 点击查看 ===== SLIDE 36 ===== STEP 01 ③ 在虚拟机Node02中创建/export/data/chapter5目录，并将包含示例数据的文件sales_fact.csv上传到该目录。 5.2 任务：执行条件查询 1. 关系运算符 STEP 01 ④ 在HDFS创建/hive/data/chapter5目录，并将文件sales_fact.csv上传到该目录，在虚拟机Node02执行如下命令。 hdfs dfs -mkdir /hive/data/chapter5 hdfs dfs -put /export/data/chapter5/sales_fact.csv /hive/data/chapter5 创建/hive/data/chapter5目录 将文件sales_fact.csv上传到/hive/data/chapter5目录 ===== SLIDE 37 ===== HiveQL语句 STEP 01 ⑤ 通过文件sales_fact.csv向分区表sales_fact加载数据，具体HiveQL语句如下。 5.2 任务：执行条件查询 1. 关系运算符 LOAD DATA INPATH '/hive/data/chapter5/sales_fact.csv' OVERWRITE INTO TABLE chapter5.sales_fact; ===== SLIDE 38 ===== 使用关系运算符=筛选出分区表sales_fact中省级行政区为Beijing的订单，返回对应的订单ID、用户ID、商品ID和省级行政区，具体HiveQL语句如下。 5.2 任务：执行条件查询 1. 关系运算符 STEP 02 HiveQL语句 SELECT order_id,user_id,item_id,province FROM chapter5.sales_fact WHERE province = 'Beijing'; ===== SLIDE 39 ===== 上述语句执行完成后的效果如图。 5.2 任务：执行条件查询 1. 关系运算符 STEP 02 ===== SLIDE 40 ===== 使用关系运算符IS NULL筛选出分区表sales_fact中设备为NULL的订单，返回对应的订单ID、用户ID、商品ID和设备，具体HiveQL语句如下。 5.2 任务：执行条件查询 1. 关系运算符 STEP 03 HiveQL语句 SELECT order_id,user_id,item_id,device FROM chapter5.sales_fact WHERE device IS NULL; ===== SLIDE 41 ===== 上述语句执行完成后的效果如图。 5.2 任务：执行条件查询 1. 关系运算符 STEP 03 ===== SLIDE 42 ===== 使用关系运算符BETWEEN AND筛选出分区表sales_fact中下单时间在2025-08-20 00:00:00和2025-08-21 23:59:59范围内的订单，返回对应的订单ID、用户ID、商品ID和下单时间，具体HiveQL语句如下。 5.2 任务：执行条件查询 1. 关系运算符 STEP 04 HiveQL语句 SELECT order_id,user_id,item_id,order_time FROM chapter5.sales_fact WHERE order_time BETWEEN timestamp '2025-08-20 00:00:00' AND timestamp '2025-08-21 23:59:59'; ===== SLIDE 43 ===== 上述语句执行完成后的效果如图。 5.2 任务：执行条件查询 1. 关系运算符 STEP 04 ===== SLIDE 44 ===== 在查询过程中，有时不仅需要筛选特定记录，还需要按照业务规则直接进行数值计算。例如，根据商品的单价和销售数量计算成交额，或根据商品的标价与实付金额计算折扣率。为此，Hive提供了一系列算术运算符，可在查询中直接执行数值运算。 5.2 任务：执行条件查询 2. 算术运算符 ===== SLIDE 45 ===== 算术运算符既可以出现在查询内容的表达式中，也可以出现在WHERE、GROUP BY或ORDER BY等子句的表达式中，为后续的关系判断和聚合汇总提供基础支持。 5.2 任务：执行条件查询 2. 算术运算符 ===== SLIDE 46 ===== 关于Hive中算术运算符的介绍如表。 5.2 任务：执行条件查询 2. 算术运算符 算术运算符 语法 操作数类型 描述 + A + B 数值类型 对A和B进行相加运算。例如，salary + 500 - A - B 数值类型 对A和B进行相减运算。例如，price - discount * A * B 数值类型 对A和B进行相乘运算。例如，quantity * unit_price / A / B 数值类型 对A和B进行相除运算，结果通常为DOUBLE类型。例如，total_sales / total_orders ===== SLIDE 47 ===== 5.2 任务：执行条件查询 2. 算术运算符 算术运算符 语法 操作数类型 描述 DIV A DIV B 整数类型 对A和B进行相除运算，结果为整数（向零取整）。例如，total_points DIV 10 % A % B 数值类型 对A和B进行取余运算。例如，order_id % 2 & A & B 数值类型 对A和B的二进制位进行逐位逻辑与运算。例如，user_flags & 1 ^ A ^ B 数值类型 对A和B的二进制位进行逐位逻辑异或运算。例如，permission_code ^ 4 ~ ~A 数值类型 对A的二进制位进行逐位取反运算。例如，~error_code ===== SLIDE 48 ===== 接下来，演示如何使用算术运算符表中的部分算术运算符进行条件查询。本案例利用算术运算符*和-计算分区表sales_fact中每个订单的金额，并筛选出金额大于1000的订单，返回对应的订单ID和订单金额，具体HiveQL语句如下。 5.2 任务：执行条件查询 2. 算术运算符 SELECT order_id, order_amount FROM ( SELECT order_id, price * quantity - discount AS order_amount FROM chapter5.sales_fact ) t WHERE order_amount > 1000; ===== SLIDE 49 ===== 上述语句执行完成后的效果如图。 5.2 任务：执行条件查询 2. 算术运算符 ===== SLIDE 50 ===== 在条件查询中，单一的筛选条件往往难以表达复杂的业务规则。实际应用场景中，常常需要对多个筛选条件进行组合，或者在条件中引入集合判断。例如，筛选出既满足特定金额要求，又符合指定日期范围的订单；筛选出商品类别属于电子产品、服装或图书的订单。这类需求仅依靠简单的关系运算符无法实现。为此，Hive提供了一系列逻辑运算符，用于支持更复杂的条件表达。 5.2 任务：执行条件查询 3. 逻辑运算符 ===== SLIDE 51 ===== 关于Hive中逻辑运算符的介绍如表。 5.2 任务：执行条件查询 逻辑运算符 语法 操作数类型 描述 AND A AND B BOOLEAN类型 若A和B均为TRUE，则返回TRUE；若任意一个操作数为FALSE，则返回FALSE。当任意一个操作数为NULL且另一个操作数不为FALSE时，返回NULL OR A OR B BOOLEAN类型 若A和B均为FALSE，则返回FALSE；若任意一个操作数为TRUE，则返回TRUE。当任意一个操作数为NULL且另一个操作数不为TRUE时，返回NULL NOT NOT A BOOLEAN类型 若A为TRUE，则返回FALSE；若A为FALSE，则返回TRUE。当A为NULL时，返回NULL 3. 逻辑运算符 ===== SLIDE 52 ===== 5.2 任务：执行条件查询 逻辑运算符 语法 操作数类型 描述 ! ! A BOOLEAN类型 与NOT的作用相同 IN A IN (val,val,…) 原生数据类型 小括号中的内容可以是常量列表，也可以是返回单字段标量值的子查询。若A存在于列表或子查询结果中，则返回TRUE；若不存在，则返回FALSE。当列表或子查询的结果中包含NULL且A不存在时，返回NULL 3. 逻辑运算符 ===== SLIDE 53 ===== 5.2 任务：执行条件查询 逻辑运算符 语法 操作数类型 描述 NOT IN A NOT IN (val,val,…) 原生数据类型 若A不存在于列表或子查询结果中，则返回TRUE；若存在，则返回FALSE。当列表或子查询的结果中包含NULL且A不存在时，返回NULL EXISTS EXISTS (subquery) — 判断子查询是否返回结果。若子查询返回至少一条记录，则返回TRUE；否则返回FALSE NOT EXISTS NOT EXISTS (subquery) — 判断子查询是否不返回结果。若子查询无记录返回，则返回TRUE；否则返回FALSE 3. 逻辑运算符 ===== SLIDE 54 ===== 接下来，演示如何使用算术运算符表中的部分逻辑运算符进行条件查询，具体内容如下。 5.2 任务：执行条件查询 3. 逻辑运算符 ===== SLIDE 55 ===== STEP 01 使用逻辑运算符AND筛选出分区表sales_fact中分区year=2025/month=09的数据，即筛选出2025年9月的订单，返回对应的订单ID和下单时间，具体HiveQL语句如下。 5.2 任务：执行条件查询 3. 逻辑运算符 HiveQL语句 SELECT order_id,order_time FROM chapter5.sales_fact WHERE year = '2025' AND month = '09'; ===== SLIDE 56 ===== STEP 01 上述语句执行完成后的效果如图。 5.2 任务：执行条件查询 3. 逻辑运算符 ===== SLIDE 57 ===== STEP 02 使用逻辑运算符AND筛选出分区表sales_fact中2025年9月且商品类型为Sports和Grocery的订单，返回对应的订单ID、商品类型和下单时间，具体HiveQL语句如下。 5.2 任务：执行条件查询 3. 逻辑运算符 HiveQL语句 SELECT order_id,category,order_time FROM chapter5.sales_fact WHERE year='2025' AND month='09' AND category IN ('Sports','Grocery'); ===== SLIDE 58 ===== STEP 02 上述语句执行完成后的效果如图。 5.2 任务：执行条件查询 3. 逻辑运算符 ===== SLIDE 59 ===== 任务：执行公共表表达式查询 5.3 ===== SLIDE 60 ===== 5.3 任务：执行公共表表达式查询 熟悉公共表表达式，能够在查询语句中定义临时结果集并实现多次引用。 学习目标 ===== SLIDE 61 ===== 公共表表达式允许用户在查询开始时定义一个或多个临时结果集，这些结果集只在当前查询中有效，并且可以被后续的SELECT语句多次引用。简单来说，公共表表达式的作用就像给复杂子查询起了一个名字，使SELECT语句更简洁、易读，同时提高了代码的复用性。 5.3 任务：执行公共表表达式查询 ===== SLIDE 62 ===== 在Hive中，公共表表达式通过SELECT语句中的WITH子句实现，其语法格式如下。 HiveQL语句 WITH cte_name AS (select_statement) [,cte_name AS (select_statement),…] 5.3 任务：执行公共表表达式查询 用于指定公共表表达式的唯一标识 用于定义公共表表达式的临时结果集 ===== SLIDE 63 ===== 接下来，演示如何使用公共表表达式，在数据库chapter5的分区表sales_fact中筛选出2025年8月和9月订单金额大于1000的订单，返回对应的订单ID和订单金额，具体HiveQL语句如下。 WITH t1 AS ( SELECT order_id,(price * quantity - discount) AS order_amount,order_time FROM chapter5.sales_fact WHERE year='2025' AND month IN ('08','09') ) SELECT order_id,order_amount,order_time FROM t1 WHERE order_amount > 1000; 5.3 任务：执行公共表表达式查询 ===== SLIDE 64 ===== 上述语句执行完成后的效果如图。 5.3 任务：执行公共表表达式查询 ===== SLIDE 65 ===== 任务：执行分组查询 5.4 ===== SLIDE 66 ===== 5.4 任务：执行分组查询 掌握分组查询，能够熟练运用GROUP BY子句对数据进行分组统计。 学习目标 ===== SLIDE 67 ===== 在实际的数据分析中，关注点往往不仅限于单条记录，而是更侧重于一组数据的整体特征。例如，可以统计每个商品的总销售额、各地区的订单数量或每个班级的平均成绩。这些问题具有一个共同点，就是查询对象不再是单行数据，而是需要按照某个字段的值对数据进行分组，并在组内执行聚合运算。 5.4 任务：执行分组查询 ===== SLIDE 68 ===== 为此，Hive在SELECT语句中提供了GROUP BY子句来实现分组查询。通过分组查询，可以将数据划分为若干组，并对每组数据执行相应的操作。通常情况下，GROUP BY子句会与聚合函数结合使用，以计算每个分组的统计结果。不过，在某些场景下也可以单独使用GROUP BY子句，例如对指定字段进行去重。 5.4 任务：执行分组查询 ===== SLIDE 69 ===== 关于GROUP BY子句的语法格式如下。 HiveQL语句 GROUP BY group_col_list 用于指定分组字段 5.4 任务：执行分组查询 需要注意的是，在SELECT语句中使用GROUP BY子句时，查询内容只能包含分组字段或聚合函数。 ===== SLIDE 70 ===== 接下来，基于数据库chapter5中的分区表sales_fact，演示如何使用GROUP BY子句实现分组查询，具体内容如下。 5.4 任务：执行分组查询 ===== SLIDE 71 ===== STEP 01 通过GROUP BY子句对分区表sales_fact中的字段province进行去重，具体HiveQL语句如下。 5.4 任务：执行分组查询 HiveQL语句 SELECT province FROM chapter5.sales_fact GROUP BY province; ===== SLIDE 72 ===== STEP 01 上述语句执行完成后的效果如图。 5.4 任务：执行分组查询 ===== SLIDE 73 ===== STEP 02 将GROUP BY子句与聚合函数SUM()结合使用，计算分区表sales_fact中每个省级行政区的订单金额，具体HiveQL语句如下。 5.4 任务：执行分组查询 HiveQL语句 SELECT province,SUM(price * quantity - discount) AS total_amount FROM chapter5.sales_fact GROUP BY province; ===== SLIDE 74 ===== STEP 02 上述语句执行完成后的效果如图。 5.4 任务：执行分组查询 ===== SLIDE 75 ===== HAVING子句 在Hive的分组查询中，通常使用GROUP BY子句将数据按照某个字段的值划分为不同分组，并结合聚合函数计算各分组的统计指标。然而，在实际分析中，我们往往并不需要所有分组的结果，而是只关心那些满足特定条件的分组。 例如，只想查看销售额大于10000元的商品类型、只想统计订单数超过100的用户等。在这种情况下，仅依靠WHERE子句是无法实现的。原因在于，WHERE子句在分组前对单行数据进行过滤，而上述需求是基于分组聚合后的结果进行过滤，因此必须使用HAVING子句。 5.4 任务：执行分组查询 ===== SLIDE 76 ===== HAVING子句 HAVING子句用于在GROUP BY子句完成分组查询后，对分组结果进行条件过滤。它通常与聚合函数配合使用，用于筛选满足条件的分组。 例如，计算分区表sales_fact中每个省级行政区的订单金额，并筛选订单金额大于1000的省级行政区，具体HiveQL语句如下。 5.4 任务：执行分组查询 WITH t1 AS ( SELECT province,(price * quantity - discount ) AS order_amount FROM chapter5.sales_fact ) SELECT province, SUM(order_amount) AS total_amount FROM t1 GROUP BY province HAVING SUM(order_amount) >1000; ===== SLIDE 77 ===== 任务：执行排序查询 5.5 ===== SLIDE 78 ===== 5.5 任务：执行排序查询 掌握排序查询，能够熟练运用ORDER BY、SORT BY等子句对数据进行排序展示。 学习目标 ===== SLIDE 79 ===== 在日常数据分析中，结果的呈现顺序往往与数据本身同等重要。一个无序的结果集虽然包含所需信息，但在阅读和使用时缺乏直观性；而经过合理排序的结果集，不仅更易于理解，还能帮助分析者快速发现规律与重点。例如，在商品分析中，排序能帮助我们快速找到销量最高或最低的商品。 5.5 任务：执行排序查询 ===== SLIDE 80 ===== Hive在SELECT语句中提供了多种子句用于实现排序查询，从而能够灵活适应不同的应用场景，具体介绍如下。 ORDER BY子句 A SORT BY子句 B CLUSTER BY子句 C 5.5 任务：执行排序查询 ===== SLIDE 81 ===== ORDER BY子句用于对查询结果进行全局排序。由于全局排序只能由一个Task完成，因此容易成为性能瓶颈。基于ORDER BY子句实现的排序查询，通常适用于小规模数据集或必须严格保证全局顺序的场景。 5.5 任务：执行排序查询 1. ORDER BY子句 ===== SLIDE 82 ===== 关于ORDER BY子句的语法格式如下。 1. ORDER BY子句 5.5 任务：执行排序查询 HiveQL语句 ORDER BY col [ASC|DESC][,col [ASC|DESC],...] 用于指定排序字段 用于指定排序规则为升序 用于指定排序规则为降序 需要注意的是，ORDER BY子句实现的是全局排序，当面对大规模数据时，可能会对集群性能造成严重影响 ===== SLIDE 83 ===== 接下来，基于数据库chapter5中的分区表sales_fact，演示如何使用ORDER BY子句实现排序查询，具体内容如下。 5.5 任务：执行排序查询 1. ORDER BY子句 ===== SLIDE 84 ===== STEP 01 使用ORDER BY子句对分区表sales_fact中的订单按照订单金额降序排序，并结合LIMIT子句获取排名前3的订单，返回对应的订单ID和订单金额，具体HiveQL语句如下。 5.5 任务：执行排序查询 1. ORDER BY子句 HiveQL语句 SELECT order_id,(price * quantity - discount ) AS order_amount FROM chapter5.sales_fact ORDER BY order_amount DESC LIMIT 3; ===== SLIDE 85 ===== STEP 01 上述语句执行完成后的效果如图。 5.5 任务：执行排序查询 1. ORDER BY子句 ===== SLIDE 86 ===== STEP 02 使用ORDER BY子句和GROUP BY子句实现分组排序查询，计算分区表sales_fact中每个省级行政区的订单金额，并按照订单金额降序排序，具体HiveQL语句如下。 5.5 任务：执行排序查询 1. ORDER BY子句 HiveQL语句 SELECT province, SUM(price * quantity - discount) AS total_amount FROM chapter5.sales_fact GROUP BY province ORDER BY total_amount DESC; ===== SLIDE 87 ===== STEP 02 上述语句执行完成后的效果如图。 5.5 任务：执行排序查询 1. ORDER BY子句 ===== SLIDE 88 ===== STEP 03 使用ORDER BY子句对分区表sales_fact中的订单先按照省级行政区升序排序，再在每个省级行政区内按照订单金额降序排序，具体HiveQL语句如下。 5.5 任务：执行排序查询 1. ORDER BY子句 HiveQL语句 SELECT province, (price * quantity - discount) AS total_amount,order_id FROM chapter5.sales_fact ORDER BY province,total_amount DESC; ===== SLIDE 89 ===== STEP 03 上述语句执行完成后的效果如图。 5.5 任务：执行排序查询 1. ORDER BY子句 ===== SLIDE 90 ===== SORT BY子句用于对查询结果进行局部排序，即根据Task的数量将数据划分并分发到不同的任务中，在各任务内部分别完成排序，从而保证每个任务内的数据有序，但不保证整体查询结果有序。 5.5 任务：执行排序查询 2. SORT BY子句 ===== SLIDE 91 ===== 由于SORT BY子句可以通过多个并行任务执行排序操作，所以在处理大规模数据时能够以分布式方式提升执行效率，相比ORDER BY子句性能更高。不过，当Task的数量为1时，例如数据量较小。SORT BY子句与ORDER BY子句的效果相同，都会对查询结果进行全局排序。 5.5 任务：执行排序查询 2. SORT BY子句 ===== SLIDE 92 ===== 在Hive中，SORT BY子句可以与DISTRIBUTE BY子句联合使用。这样不仅能够保证根据指定字段值进行哈希取模后结果相同的记录被分配到同一个Task，还可以在每个Task内部再按照指定字段的值进行排序。 5.5 任务：执行排序查询 2. SORT BY子句 ===== SLIDE 93 ===== 关于SORT BY子句的语法格式如下。 5.5 任务：执行排序查询 语法格式 SORT BY col [ASC|DESC][,col [ASC|DESC],...] 2. SORT BY子句 ===== SLIDE 94 ===== 接下来，基于数据库chapter5中的分区表sales_fact，演示如何使用SORT BY子句实现排序查询，具体内容如下。 5.5 任务：执行排序查询 2. SORT BY子句 ===== SLIDE 95 ===== STEP 01 由于分区表sales_fact的数据量较小，而Hive默认将每个Task需要处理的数据量估算为256000000字节（即256MB），所以在使用SORT BY子句进行排序查询时，实际上只会启动一个Task，其最终效果与ORDER BY子句相同。 为演示并行执行的效果，这里在当前会话中临时修改参数，将Hive估算的每个Task目标处理数据量调整为64字节，从而强制通过多个并行Task执行SORT BY子句实现的排序查询，具体HiveQL语句如下。 2. SORT BY子句 5.5 任务：执行排序查询 SET hive.exec.reducers.bytes.per.reducer=64; 用于控制Hive估算每个Task预计处理的数据量 ===== SLIDE 96 ===== STEP 02 使用SORT BY子句对分区表sales_fact中的订单按照订单金额降序排序，返回对应的订单ID和订单金额，具体HiveQL语句如下。 2. SORT BY子句 5.5 任务：执行排序查询 SELECT order_id,(price * quantity - discount ) AS order_amount FROM chapter5.sales_fact SORT BY order_amount DESC; ===== SLIDE 97 ===== STEP 02 上述语句执行完成后的效果如图。 2. SORT BY子句 5.5 任务：执行排序查询 ===== SLIDE 98 ===== STEP 03 使用DISTRIBUTE BY子句和SORT BY子句实现排序查询，将省级行政区哈希取模结果相同的记录分发到同一个Task，并在每个Task内部按照订单金额升序排序，具体HiveQL语句如下。 2. SORT BY子句 5.5 任务：执行排序查询 SELECT order_id,(price * quantity - discount ) AS order_amount,province FROM chapter5.sales_fact DISTRIBUTE BY province SORT BY order_amount; ===== SLIDE 99 ===== STEP 03 上述语句执行完成后的效果如图。 2. SORT BY子句 5.5 任务：执行排序查询 ===== SLIDE 100 ===== STEP 03 CLUSTER BY子句等价于DISTRIBUTE BY子句和SORT BY子句联合使用，且二者指定字段相同的效果。但需要注意，CLUSTER BY子句不支持显式指定排序规则，始终采用默认的升序排序。 3. CLUSTER BY子句 5.5 任务：执行排序查询 ===== SLIDE 101 ===== 任务：执行合并查询 5.6 ===== SLIDE 102 ===== 5.6 任务：执行合并查询 了解合并查询，能够使用UNION语句对两张表的数据进行合并。 学习目标 ===== SLIDE 103 ===== 在实际数据分析中，经常需要将来自不同查询的结果合并，以便统一展示。例如，企业可能将线上订单和线下订单分别存储在不同的表中，而财务部门往往希望同时查看线上与线下的销售汇总。如果仅依次执行各个查询，就只能得到零散的结果，难以实现整体分析。此时，就需要一种机制能够将多个查询结果纵向拼接，从而生成一个完整的结果集。 5.6 任务：执行合并查询 ===== SLIDE 104 ===== 为此，Hive提供了UNION语句来实现合并查询。借助合并查询，可以在一条HiveQL语句中整合来自多个数据源或不同查询条件的结果。此外，UNION语句可以作为SELECT语句的子句，从而对合并查询的结果进一步分析。 5.6 任务：执行合并查询 ===== SLIDE 105 ===== 关于UNION语句的语法格式如下。 语法格式 select_statement UNION ALL | DISTINCT select_statement [UNION ALL | DISTINCT select_statement ...] 5.6 任务：执行合并查询 用于指定查询语句 用于在合并两侧的结果集时，去除重复行 ===== SLIDE 106 ===== 接下来，演示如何使用UNION语句实现合并查询，具体内容如下。 5.6 任务：执行合并查询 ===== SLIDE 107 ===== STEP 01 为便于后续演示如何整合来自多个数据源的查询结果，在数据库chapter5中创建一个与分区表sales_fact结构相同的分区表sales_fact_new，具体HiveQL语句如下。 5.6 任务：执行合并查询 语法格式 CREATE TABLE IF NOT EXISTS chapter5.sales_fact_new LIKE chapter5.sales_fact; ===== SLIDE 108 ===== STEP 02 使用INSERT语句向分区表sales_fact_new插入示例数据，具体HiveQL语句如下。 5.6 任务：执行合并查询 点击查看 ===== SLIDE 109 ===== STEP 03 分别计算分区表sales_fact_new和sales_fact中每个订单的订单金额，返回对应的订单ID和订单金额，并通过UNION语句将两个查询结果合并为一个统一的结果集，具体HiveQL语句如下。 5.6 任务：执行合并查询 SELECT order_id,(price * quantity - discount ) AS order_amount FROM chapter5.sales_fact UNION ALL SELECT order_id,(price * quantity - discount ) AS order_amount FROM chapter5.sales_fact_new; ===== SLIDE 110 ===== STEP 03 上述语句执行完成后的效果如图。 5.6 任务：执行合并查询 ===== SLIDE 111 ===== STEP 04 分别计算分区表sales_fact_new和sales_fact中每个订单的订单金额，返回对应的省份和订单金额，并通过UNION语句将两个查询结果合并为一个统一的结果集。在此基础上进一步实现分组排序查询，计算每个省级行政区的订单金额，并按照订单金额降序排序，具体HiveQL语句如下。 5.6 任务：执行合并查询 点击查看 ===== SLIDE 112 ===== STEP 04 上述语句执行完成后的效果如图。 5.6 任务：执行合并查询 ===== SLIDE 113 ===== 任务：执行连接查询 5.7 ===== SLIDE 114 ===== 5.7 任务：执行连接查询 掌握连接查询，能够熟练运用JOIN语句实现内连接、左外连接、右外连接等操作。 学习目标 ===== SLIDE 115 ===== 在实际的数据仓库应用中，为了满足数据规范化存储和高效管理的需求，企业往往会将数据拆分到多张表中。例如，用户的基本信息存储在用户表，交易记录存储在订单表，商品的详细资料存储在商品表。 5.7 任务：执行连接查询 ===== SLIDE 116 ===== 在这种场景下，当需要进行综合分析时，单表查询往往难以满足需求。例如，若要查询某个用户的订单详情，就必须将用户表与订单表进行关联；若要统计不同商品类别的销售额，就需要将订单表与商品表进行关联。 5.7 任务：执行连接查询 ===== SLIDE 117 ===== 这类需求要求我们能够在一条查询语句中将两张表的数据进行关联。为此，Hive提供了JOIN语句来实现连接查询。借助连接查询，可以按照一个或多个公共字段将不同表的数据关联，从而在查询结果中得到完整的信息。所谓公共字段，是指两张表中含义相同且能够作为关联条件的字段。例如，在用户表与订单表的连接查询中，可以使用两张表的用户ID作为公共字段，从而将用户信息与其对应的订单记录关联起来。 5.7 任务：执行连接查询 ===== SLIDE 118 ===== Hive支持多种连接方式，包括内连接、左外连接、右外连接、全外连接、左半连接与笛卡尔积连接，在复杂查询中，可以根据业务需求组合使用不同连接方式，但应注意连接顺序和结果语义。在介绍不同连接方式时，通常将参与关联的两张表称为左表和右表，这一称谓来源于JOIN语句的书写顺序，其中位于JOIN关键字左侧的表被称为左表，而位于右侧的表则被称为右表。 5.7 任务：执行连接查询 ===== SLIDE 119 ===== 关于Hive中不同连接方式的介绍如下。 5.7 任务：执行连接查询 ===== SLIDE 120 ===== 内连接只返回两张表中满足连接条件的记录。所谓连接条件，是指两表在公共字段上的取值是否满足预设规则，最常见的情况是等值匹配。当两张表中的记录满足连接条件时，Hive会将两条记录的字段拼接在一起生成一条新记录。 5.7 任务：执行连接查询 1. 内连接 ===== SLIDE 121 ===== 例如，用户表和订单表根据公共字段用户ID执行内连接的效果如图。 1. 内连接 5.7 任务：执行连接查询 ===== SLIDE 122 ===== 在Hive中内连接的语法格式如下。 SELECT select_expr,select_expr,... FROM [db_name.]table_name1 [INNER] JOIN [db_name.]table_name2 ON expression [[INNER] JOIN [db_name.]table_name3 ON expression ...] 5.7 任务：执行连接查询 1. 内连接 用于指定从结果集中提取的内容 用于指定连接条件 ===== SLIDE 123 ===== 左外连接会返回左表中的所有记录。若两张表中的记录满足连接条件，则将它们的字段拼接生成一条新记录；若左表中的某条记录在右表中没有匹配到满足连接条件的记录，该记录仍会保留，并与右表中字段以NULL填充后的记录拼接生成一条新记录。 5.7 任务：执行连接查询 2. 左外连接 ===== SLIDE 124 ===== 例如，用户表和订单表根据公共字段用户ID执行左外连接的效果如图。 5.7 任务：执行连接查询 2. 左外连接 ===== SLIDE 125 ===== 在Hive中左外连接的语法格式如下。 SELECT select_expr,select_expr,... FROM [db_name.]table_name1 LEFT [OUTER] JOIN [db_name.]table_name2 ON expression [LEFT [OUTER] JOIN [db_name.]table_name3 ON expression ...] 5.7 任务：执行连接查询 2. 左外连接 ===== SLIDE 126 ===== 右外连接会返回右表中的所有记录。若两张表中的记录满足连接条件，则将它们的字段拼接生成一条新记录；若右表中的某条记录在左表中没有匹配到满足连接条件的记录，该记录仍会保留，并与左表中字段以NULL填充后的记录拼接生成一条新记录。 5.7 任务：执行连接查询 3. 右外连接 ===== SLIDE 127 ===== 例如，用户表和订单表根据公共字段用户ID执行右外连接的效果如图。 5.7 任务：执行连接查询 3. 右外连接 ===== SLIDE 128 ===== 在Hive中右外连接的语法格式如下。 SELECT select_expr,select_expr,... FROM [db_name.]table_name1 RIGHT [OUTER] JOIN [db_name.]table_name2 ON expression [RIGHT [OUTER] JOIN [db_name.]table_name3 ON expression ...] 5.7 任务：执行连接查询 3. 右外连接 ===== SLIDE 129 ===== 全外连接会返回两张表中的所有记录。若两张表中的记录满足连接条件，则将它们的字段拼接生成一条新记录；若左表中的某条记录在右表中没有匹配到满足连接条件的记录，该记录仍会保留，并与右表中字段以NULL填充后的记录拼接生成一条新记录；同样地，若右表中的某条记录在左表中没有匹配到满足连接条件的记录，该记录也会保留，并与左表中字段以NULL填充后的记录拼接生成一条新记录。 5.7 任务：执行连接查询 4. 全外连接 ===== SLIDE 130 ===== 例如，用户表和订单表根据公共字段用户ID执行全外连接的效果如图。 5.7 任务：执行连接查询 4. 全外连接 ===== SLIDE 131 ===== 在Hive中全外连接的语法格式如下。 SELECT select_expr,select_expr,... FROM [db_name.]table_name1 FULL [OUTER] JOIN [db_name.]table_name2 ON expression [FULL [OUTER] JOIN [db_name.]table_name3 ON expression ...] 5.7 任务：执行连接查询 4. 全外连接 ===== SLIDE 132 ===== 左半连接只返回左表中满足连接条件的记录。若左表中的某条记录在右表中没有匹配到满足连接条件的记录，则该记录会被丢弃。 5.7 任务：执行连接查询 5. 左半连接 ===== SLIDE 133 ===== 例如，用户表和订单表根据公共字段用户ID执行左半连接的效果如图。 5.7 任务：执行连接查询 5. 左半连接 ===== SLIDE 134 ===== 在Hive中左半连接的语法格式如下。 SELECT select_expr,select_expr,... FROM [db_name.]table_name1 LEFT SEMI JOIN [db_name.]table_name2 ON expression [LEFT SEMI JOIN [db_name.]table_name3 ON expression ...] 5.7 任务：执行连接查询 5. 左半连接 ===== SLIDE 135 ===== 笛卡尔积连接没有连接条件。Hive会将左表中的每一条记录与右表中的每一条记录进行组合，并将它们的字段拼接在一起，生成一条新记录。结果集的总行数等于左表行数乘以右表行数，这种全组合的结果往往会非常庞大，因此通常只在测试或特殊分析中使用。 5.7 任务：执行连接查询 6. 笛卡尔积连接 ===== SLIDE 136 ===== 例如，用户表和订单表执行笛卡尔积连接的效果如图。 5.7 任务：执行连接查询 6. 笛卡尔积连接 ===== SLIDE 137 ===== 在Hive中笛卡尔积连接的语法格式如下。 5.7 任务：执行连接查询 6. 笛卡尔积连接 SELECT select_expr,select_expr,... FROM [db_name.]table_name1 CROSS JOIN [db_name.]table_name2 [CROSS JOIN [db_name.]table_name3 ...] ===== SLIDE 138 ===== 接下来，演示如何使用JOIN语句实现连接查询，具体内容如下。 5.7 任务：执行连接查询 ===== SLIDE 139 ===== STEP 01 为便于演示连接查询，在数据库chapter5中创建一个存储用户信息的表users，该表包含用户的基本信息，用于与存储订单信息的分区表sales_fact进行关联，具体HiveQL语句如下。 点击查看 5.7 任务：执行连接查询 ===== SLIDE 140 ===== STEP 02 使用INSERT语句向表users插入示例数据，具体HiveQL语句如下。 点击查看 5.7 任务：执行连接查询 ===== SLIDE 141 ===== STEP 03 将分区表sales_fact与表users通过内连接的方式关联，筛选出2025年8月的订单及其对应的用户基本信息，返回订单ID、商品ID、用户姓名、用户性别、用户最近登录时间和用户出生日期，具体HiveQL语句如下。 点击查看 5.7 任务：执行连接查询 ===== SLIDE 142 ===== STEP 03 上述语句执行完成后的效果如图。 5.7 任务：执行连接查询 ===== SLIDE 143 ===== 从逻辑处理角度看，连接条件会先决定表之间的匹配关系，WHERE子句再对连接结果进行过滤。因此，如果只是希望在连接查询完成后对结果进行过滤，可以在JOIN语句的结尾追加WHERE子句设置筛选条件。但如果筛选条件需要影响连接查询，则需要在连接条件中明确指定。 此外，在对分区表进行左外连接、右外连接或全外连接时，如果未满足连接条件，就会在结果中产生NULL。若此时通过WHERE子句设置筛选条件来限定查询分区，则会直接过滤掉包含NULL的记录，从而破坏左外连接、右外连接或全外连接的语义。因此，当需要限定分区时，应将其写在JOIN语句的连接条件中，而不是写在WHERE子句中。 5.7 任务：执行连接查询 连接查询中的WHERE子句 ===== SLIDE 144 ===== 例如，下面的HiveQL语句在连接条件中同时指定了公共字段的匹配规则和分区限制。 5.7 任务：执行连接查询 连接查询中的WHERE子句 具体语法 SELECT a.val, b.val FROM a LEFT JOIN b ON a.key=b.key AND b.ds='2025-07-07' AND a.ds='2025-07-07'; ===== SLIDE 145 ===== 任务：执行抽样查询 5.8 ===== SLIDE 146 ===== 5.8 任务：执行抽样查询 了解抽样查询，能够使用字节数抽样、行抽样等方式从表中查询部分数据。 学习目标 ===== SLIDE 147 ===== 在处理大规模数据时，并非所有场景都需要对全量数据进行分析。在测试、调试或预分析阶段，我们通常更关注查询逻辑是否正确、结果趋势是否合理，而不是具体的全量结果。这时，从海量数据中抽取一部分具有代表性的样本即可满足需求。为了支持这种场景，Hive提供了抽样查询机制，即按照特定规则只选取部分数据返回，从而得到原始数据的一个子集。 5.8 任务：执行抽样查询 ===== SLIDE 148 ===== 5.8 任务：执行抽样查询 Hive中的抽样主要包括字节数抽样、百分比抽样、行抽样和分桶抽样，具体介绍如下。 字节样抽样 百分比抽样 行抽样 分桶抽样 Hive 抽样 ===== SLIDE 149 ===== 字节数抽样是一种基于HDFS数据块（Block）的抽样方式，用于从表中抽取不少于指定字节数的数据块集合。例如，若指定字节数为100MB，则Hive会在表对应的数据文件中随机选取若干数据块，使其累计大小不小于100MB。由于样本单位是完整数据块，实际数据量可能大于指定字节数。 1. 字节数抽样 5.8 任务：执行抽样查询 ===== SLIDE 150 ===== 具体实现过程为，首先Hive会统计目标表的数据总量，并根据设定的字节数推算出大致需要的数据块个数。然后，系统选择相应数量的数据块作为输入，以确保数据块的数据总量不低于指定的字节数。 1. 字节数抽样 5.8 任务：执行抽样查询 ===== SLIDE 151 ===== 关于字节数抽样的语法格式如下。 SELECT select_expr,select_expr,... FROM [db_name.]table_name TABLESAMPLE(size); 5.8 任务：执行抽样查询 1. 字节数抽样 用于指定目标字节数，并需带有单位 ===== SLIDE 152 ===== 接下来，演示如何通过字节数抽样的方式对数据库chapter5中的分区表sales_fact实现抽样查询，具体内容如下。 1. 字节数抽样 5.8 任务：执行抽样查询 ===== SLIDE 153 ===== STEP 01 为了确定字节数抽样时指定的字节数是否合理，可以先查看分区表sales_fact的完整信息，从而获取该分区表的数据总量，如图。 5.8 任务：执行抽样查询 1. 字节数抽样 ===== SLIDE 154 ===== STEP 02 通过SET语句在当前会话中临时指定Tez使用的InputFormat实现类为org.apache.hadoop.hive.ql.io.CombineHiveInputFormat，具体HiveQL语句如下。 5.8 任务：执行抽样查询 1. 字节数抽样 SET hive.tez.input.format=org.apache.hadoop.hive.ql.io.CombineHiveInputFormat; ===== SLIDE 155 ===== STEP 03 通过SET语句在当前会话中临时关闭自动触发FetchTask，具体HiveQL语句如下。 5.8 任务：执行抽样查询 1. 字节数抽样 set hive.fetch.task.conversion=none; HiveQL语句 ===== SLIDE 156 ===== STEP 04 抽取分区表sales_fact中100字节的数据，并返回订单ID、年和月，具体HiveQL语句如下。 5.8 任务：执行抽样查询 1. 字节数抽样 SELECT order_id,year,month FROM chapter5.sales_fact TABLESAMPLE(100B); HiveQL语句 ===== SLIDE 157 ===== STEP 04 上述语句执行完成后的效果如图。 5.8 任务：执行抽样查询 1. 字节数抽样 ===== SLIDE 158 ===== 百分比抽样同样是一种基于HDFS数据块（Block）的抽样方式，用于从表中抽取不少于指定比例的数据块集合。具体实现过程为，首先Hive会统计目标表的数据总量，并根据设定比例换算出需要抽取的大致字节数。然后，系统选择相应数量的数据块作为输入，以确保数据块的数据总量不低于字节数。 2. 百分比抽样 5.8 任务：执行抽样查询 ===== SLIDE 159 ===== 关于百分比抽样的语法格式如下。 5.8 任务：执行抽样查询 SELECT select_expr,select_expr,... FROM [db_name.]table_name TABLESAMPLE(p PERCENT); 语法格式 2. 百分比抽样 用于指定比例 ===== SLIDE 160 ===== 接下来，演示如何通过百分比抽样的方式对数据库chapter5中的分区表sales_fact实现抽样查询，具体内容如下。 2. 百分比抽样 5.8 任务：执行抽样查询 ===== SLIDE 161 ===== STEP 01 在当前会话中，通过SET语句临时指定Tez使用的InputFormat实现类为org.apache.hadoop.hive.ql.io.CombineHiveInputFormat，同时关闭自动触发FetchTask，具体HiveQL语句可参考前面对字节数抽样的演示操作。 5.8 任务：执行抽样查询 2. 百分比抽样 ===== SLIDE 162 ===== STEP 02 抽取分区表sales_fact中80%的数据，并返回订单ID、年和月，具体HiveQL语句如下。 5.8 任务：执行抽样查询 SELECT order_id,year,month FROM chapter5.sales_fact TABLESAMPLE(80 PERCENT); HiveQL语句 2. 百分比抽样 ===== SLIDE 163 ===== STEP 02 上述语句执行完成后的效果如图。 5.8 任务：执行抽样查询 2. 百分比抽样 ===== SLIDE 164 ===== 行抽样是一种基于分片（Split）的抽样方式，用于从每个分片中抽取指定数量的行。分片是执行引擎在读取表文件时逻辑上划分任务的基本单元。默认情况下，分片大小接近 HDFS 数据块的大小（默认128 MB）。当文件小于128MB时，会生成一个Split。当文件大于128MB时，会根据文件大小被划分为多个Split。 5.8 任务：执行抽样查询 3. 行抽样 ===== SLIDE 165 ===== 关于行抽样的语法格式如下。 5.8 任务：执行抽样查询 SELECT select_expr,select_expr,... FROM [db_name.]table_name TABLESAMPLE(n ROWS); 语法格式 3. 行抽样 用于指定行数 需要注意的是，若行抽样通过FetchTask执行，则无论是分区表还是非分区表都仅会从全表返回前两行数据。 ===== SLIDE 166 ===== 接下来，演示如何通过行抽样的方式对数据库chapter5中的分区表sales_fact实现抽样查询，具体内容如下。 5.8 任务：执行抽样查询 3. 行抽样 ===== SLIDE 167 ===== STEP 01 在当前会话中，关闭自动触发FetchTask，具体HiveQL语句可参考前面对字节数抽样的演示操作。 5.8 任务：执行抽样查询 3. 行抽样 STEP 02 抽取分区表sales_fact两行数据，并返回订单ID、年和月，具体HiveQL语句如下。 SELECT order_id,year,month FROM chapter5.sales_fact TABLESAMPLE(2 ROWS); ===== SLIDE 168 ===== STEP 02 使用InputFormat实现类为org.apache.hadoop.hive.ql.io.HiveInputFormat时，上述语句执行完成后的效果如图。 5.8 任务：执行抽样查询 3. 行抽样 ===== SLIDE 169 ===== 分桶抽样是一种基于分桶的抽样方式。其基本原理是将数据随机分配到预定义数量的桶（Bucket）中，每个桶按顺序编号，起始值为1，依次递增。抽样时只选择指定编号的桶，并返回该桶内的全部数据作为样本。 5.8 任务：执行抽样查询 4. 分桶抽样 ===== SLIDE 170 ===== 关于分桶抽样的语法格式如下。 5.8 任务：执行抽样查询 SELECT select_expr,select_expr,... FROM [db_name.]table_name TABLESAMPLE(BUCKET x OUT OF y [ON bucket_col]); 4. 分桶抽样 用于指定桶的编号 用于指定逻辑分桶的数量 用于显式指定分桶字段 ===== SLIDE 171 ===== 接下来，演示如何通过分桶抽样的方式对数据库chapter5中的分区表sales_fact实现抽样查询，具体内容如下。 5.8 任务：执行抽样查询 4. 分桶抽样 SELECT order_id,year,month FROM chapter5.sales_fact TABLESAMPLE(BUCKET 2 OUT OF 3 ON category); ===== SLIDE 172 ===== 上述语句执行完成后的效果如图。 5.8 任务：执行抽样查询 4. 分桶抽样 ===== SLIDE 173 ===== 随机抽样 除了Hive内置的四种抽样方式外，还可以在查询语句中利用DISTRIBUTE BY子句和SORT BY子句结合RAND()函数，并配合LIMIT子句实现随机抽取。该方式是先将目标表的数据随机分发到不同任务中，再在每个任务内进行随机排序，最后从结果集中返回前N条记录。 5.8 任务：执行抽样查询 ===== SLIDE 174 ===== 随机抽样 例如，对数据库chapter5中的分区表sales_fact执行随机抽取，并限制结果集返回前5条记录，具体HiveQL语句如下。 5.8 任务：执行抽样查询 SELECT * FROM chapter5.sales_fact DISTRIBUTE BY RAND() SORT BY RAND() LIMIT 5; DISTRIBUTE BY子句和SORT BY子句不能用CLUSTER BY子句代替。同时，LIMIT子句是必须的，不可省略。 ===== SLIDE 175 ===== 随机抽样 在执行随机抽样时，还可以通过为RAND()函数指定整数作为种子，使结果集具有可复现性。当种子值保持不变时，每次执行都会返回相同的结果。 例如，对数据库chapter5中的分区表sales_fact执行随机抽取，分别为DISTRIBUTE BY子句和SORT BY子句中的RAND()函数设定种子为2和3，并限制结果集返回前5条记录，具体HiveQL语句如下。 5.8 任务：执行抽样查询 SELECT * from chapter5.sales_fact DISTRIBUTE BY RAND() SORT BY RAND() LIMIT 5; ===== SLIDE 176 ===== AI助学 学完本单元后，我已经初步掌握了Hive中数据查询的基本方法。然而，在面对实际分析需求时，仍可能难以判断查询语句应如何组织，筛选、分组、排序、合并、连接和抽样等操作应如何配合使用。此时，可以借助AI对分析需求和查询思路进行梳理，帮助判断HiveQL语句的结构是否合理，并提示可能存在的问题。 AI 助 学 ===== SLIDE 177 ===== 单元总结 本单元主要讲解了Hive的数据查询语言。首先，介绍了SELECT语句的语法结构。然后，讲解了常见的查询方式，包括条件查询、公共表表达式、分组查询、排序查询、合并查询、连接查询和抽样查询。通过本单元的学习，读者能够熟练编写HiveQL查询语句查询Hive表中的数据，为实际数据仓库分析应用提供支撑。 单 元 总 结